<?php
class Company extends AppModel
{
    var $name = 'Company';
    var $primaryKey = 'id'; 
    var $hasMany = array (
            'pros_cvalue' => array (
            'className' => 'ProCvalue',
            'foreignKey' => 'pid',
            ),
            'CompType' => array (
            'className' => 'CompType',
            'foreignKey' => 'cid',
            ),
            'Informationfrom' => array (
			'className' => 'Informationfrom',
			'foreignKey' => 'pid',
    		),
            'Attachment' => array (
            'conditions' => array("Attachment.is_deleted='0'"),
            'className' => 'Attachment',
            'foreignKey' => 'pid'
            ),
            'CompTalent'=>array(
            'className'=>'CompTalent',
            'conditions' => array("CompTalent.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompFinacerelease'=>array(
            'className'=>'CompFinacerelease',
            'conditions' => array("CompFinacerelease.is_deleted = '0'"),
            'foreignKey'=>'cid'
            ),
            'CompSupportFund' => array (
			'className' => 'CompSupportFund',
			'foreignKey' => 'cid',
    		),
        );
    var $belongsTo = array(
        'Logo' => array(
            'className'    => 'Attachment',
            'foreignKey'    => 'company_logo'
        )
    );
    
    
    //企业注册资金如果没到位每两年提前两个月提醒企业,在/app/vendor/中的alert.php调用
    function alertmessage()
    {
        $this->unbindModel(array ('hasMany' => array ('Attachment','pros_cvalue','CompType','CompTalent','CompFinacerelease'),
                                  'belongsTo'=>array('Logo')
                                  )
                           );
        $companyinfos = $this->find("all",array('conditions'=>array("Company.is_deleted='0'","Company.is_funds='0'"),
                                                'fields'=>array("Company.id,Company.first_owner,Company.reg_time,Company.is_funds,Company.hasBeenAlerted")			
                                            ));
        foreach($companyinfos as $companyinfo)
        {
            $company_funds_alert = false;
            $company_reg_time = $companyinfo["Company"]["reg_time"];
            $company_id = $companyinfo["Company"]["id"];
            $hasBeenAlerted = $companyinfo["Company"]["hasBeenAlerted"];
            $months = intval((time()-strtotime($company_reg_time))/30/24/3600);
            if($months >= 22 && $hasBeenAlerted == '0')
            {
                $company_funds_alert = true;
                App::import('Helper', 'Accp');
                $accp = new AccpHelper();
                $data=array("guid"=>$company_id,"event"=>"no_funds","usertype"=>"1","companyFundsAlert"=>$company_funds_alert);
                $isflag=$accp->accpalert($data);
                //新的提醒方式，由于需求方尚未提出，暂不需要
//						$cid = $company_id;
//						$company = ClassRegistry::init('Company')->findById($cid);
//						$pid = $company['Company']['pid'];
//						$project = ClassRegistry::init('Project')->findById($pid);
//						$first_owner = $project['Project']['first_owner'];
//						$user = ClassRegistry::init('User')->findByCid($cid);
//						$user_id = $user['User']['id'];
//						
//						$msgdata = array('subject'=>'注册资金尚未到位','content'=>'尊敬的客户您好，您在大科园的注册资金尚未到位，特此通知！','owner_id'=>1,'is_readed'=>0,'has_feedback'=>0,'auto_sys'=>0,'id_deleted'=>0);
//						$msg_id = ClassRegistry::init('Msg')->addalertmsg($msgdata);
//						
//						$rsdata_first_owner = array('id_one'=>$first_owner,'relationship'=>'message','id_two'=>$msg_id,'is_readed'=>0);
//						$rsdata_user_id = array('id_one'=>$user_id,'relationship'=>'message','id_two'=>$msg_id,'is_readed'=>0);
//						ClassRegistry::init('User_msg_relationship')->addalertrelationship($rsdata_first_owner);
//						ClassRegistry::init('User_msg_relationship')->addalertrelationship($rsdata_user_id);
//						
                $this->id = $company_id;
                $this->saveField('hasBeenAlerted', '1');
            }
        }	
    }
    
    //统计出所有产业所拥有的企业数，占地面积以及资金综合，来画出柱状图和饼图
    function getIndComp(){
        $rate = classregistry::init('Codetype')->getrate();
        $industry = classregistry::init('Constant')->Industry;
        $sql = "select codevalues.id,codevalues.value,codevalues.sort,count(companies.id) as comps,
                sum((case cap_type when '1' then capital*(".$rate.") else capital end))as capital,
                sum(comp_carrierleases.area) as size
                from codevalues left join (pro_cvalues join  companies on companies.id = pro_cvalues.pid  and companies.is_deleted='0' ) 
                on codevalues.id = pro_cvalues.cvid 
                left join comp_carrierleases  on companies.id = comp_carrierleases .cid  and comp_carrierleases.is_deleted ='0'
                where codetype_id =(select DISTINCT id from codetypes where type='".$industry."')  
                group by codevalues.id,codevalues.value
                order by codevalues.sort asc ";
        $ind_comp = $this->query($sql);
        return $ind_comp;
    }
    
    //以模板为单位查找
    function getIndCompByGroupId($groupid){
        $rate = classregistry::init('Codetype')->getrate();
        $industry = classregistry::init('Constant')->Industry;
        $sql = "select codevalues.id,codevalues.value,codevalues.sort,count(companies.id) as comps,
                sum((case cap_type when '1' then capital*(".$rate.") else capital end))as capital,
                sum(comp_carrierleases.area) as size
                from codevalues left join (pro_cvalues join  companies on companies.id = pro_cvalues.pid  and companies.is_deleted='0' ) 
                on codevalues.id = pro_cvalues.cvid 
                left join comp_carrierleases  on companies.id = comp_carrierleases .cid  and comp_carrierleases.is_deleted ='0'
                where codetype_id =(select DISTINCT id from codetypes where type='".$industry."') and groupid = '".$groupid."'  
                group by codevalues.id,codevalues.value
                order by codevalues.sort asc ";
        $ind_comp = $this->query($sql);
        return $ind_comp;
    }    
    
//统计出所有企业信息来源，画企业来源统计图
    function getInfoSourc($groupid=null){
    //	$source_type = classregistry::init('Codetype')->getCValues("信息来源");
    //	unset($source_type[""]);
        $rate = classregistry::init('Codetype')->getrate();
        //echo $rate;exit;
		if($groupid!=null){
			$sql="
				select DISTINCT  codevalues.value, count(*) comps ,
				SUM((CASE cap_type when '1' THEN capital*(".$rate.") ELSE capital END))AS capital,
				SUM(comp_carrierleases.area) AS size
				from informationfroms 
				inner JOIN  codevalues on codevalues.id = informationfroms.cvid
				JOIN  companies ON companies.is_deleted='0' and informationfroms.pid=companies.id 
				LEFT JOIN comp_carrierleases  ON companies.id = comp_carrierleases .cid  AND comp_carrierleases.is_deleted ='0'
				where companies.groupid=".$groupid." 
				GROUP BY informationfroms.cvid
				";	
		}else{
			$sql="
				select DISTINCT  codevalues.value, count(*) comps ,
				SUM((CASE cap_type when '1' THEN capital*(".$rate.") ELSE capital END))AS capital,
				SUM(comp_carrierleases.area) AS size
				from informationfroms 
				inner JOIN  codevalues on codevalues.id = informationfroms.cvid
				JOIN  companies ON companies.is_deleted='0' and informationfroms.pid=companies.id 
				LEFT JOIN comp_carrierleases  ON companies.id = comp_carrierleases .cid  AND comp_carrierleases.is_deleted ='0'
				GROUP BY informationfroms.cvid
			";	
		}
    	
        $ind_comp = $this->query($sql);
        return $ind_comp;
    }
    
    function getCompIdBySource($sourcename){
    	$source_type = classregistry::init('Codetype')->getCValues("信息来源");
    	foreach($source_type as $key => $value){
    		if($value==$sourcename){
				return $key;    			
    		}
    	}
    }
    
    
    
    
    
    function getIndCompId($indname=null){
    	$inds=$this->getIndComp();
    	for($i=0;$i<count($inds);$i++){
    		if($inds[$i]["codevalues"]["value"]==$indname){
    			return $inds[$i]["codevalues"]["id"];
    		//	return $inds[$i]["id"];
    		}
    	}
    }
    
    
    
    
    //科技项目奖励汇总
    function gettech($status,$where=''){		
        $sql = "select ifnull(level,'总计') level,ifnull(name,'合计') name,project_time,companies.company_name,companies.groupid,
                sum(fund) as fund from comp_otherdeclares
                inner join companies on comp_otherdeclares.cid=companies.id
                where level<>'' and status='".$status."'".$where."
                group by level,name with rollup";
        $ind_comp = $this->query($sql);
        return $ind_comp;
    }
    
    //在用户管理中删除用户，根据不同用户类型，对各表进行删除操作
    function deleteUser($usertype,$email){
        $table = "";
        if($usertype == 2){
            $table = "companies";
        }
        if($usertype == 3){
            $table = "comp_people";
        }
        if($usertype == 4){
            $table = "colleages";
        }
        if($usertype == 5){
            $table = "cg_people";
        }
        $sql = " update ".$table." set is_deleted = '1'  where email = '".$email."'";
        $this->query($sql);
    }
    
    function delRelatedToCompany($cid)
    {
        $tables = array("company_patents","comp_carrierleases","comp_demands","comp_dormitoryleases","comp_economics",
                        "comp_finacereleases","comp_financs","comp_jobapplies","comp_learentbonuses","comp_otherdeclares",
                        "comp_pages","comp_people","comp_researchrentbonuses","comp_talents","comp_technologies",
                        "development_costs","development_lists"
                       );
        foreach($tables as $table)
        {
            $sql =  " update ".$table." set is_deleted = '1'  where cid = '".$cid."'";
            $this->query($sql);
        }
    }
    
    //删除accp企业用户、企业联系人、高校用户、高校联系人
    function deleteAccpUser($objusers,$accp,$email){
        $userid=$accp->getuserid($objusers,$email);
        $data=array("guid"=>$userid,"is_deleted"=>"1");
        $accp->accpactionuser($data);
    }
    
    //入驻企业数据指标统计报表数据
    function totaldataReport($year, $month,$where=""){
        $codeType = classregistry::init('Codetype');
        $constant = classregistry::init('Constant');
        $rate = $codeType->getrate();
        $industry = $constant->Industry;
        $where.= "and comp_economics.company_year = '".$year."' and comp_economics.month = '".$month."'";
        //查询comp_economics表中的每个产业的产值 销售收入 利税总额 利润总额
        $ecnomic_sql = "select codevalues.id,codevalues.value,codevalues.sort,sum(t.prospect)as prospect,
                               sum(t.income) as income,sum(t.tax) as tax,sum(t.profit) as profit 
                        from codevalues left join ( pro_cvalues join (select companies.id as cid,comp_economics.prospect as prospect,
                                                                             comp_economics.general_income as income,
                                                                             comp_economics.pro_taxe as tax,comp_economics.net_profit as profit
                                                                      from companies left join comp_economics on companies.id = comp_economics.cid
                                                                      where companies.is_deleted = '0' and comp_economics.is_deleted = '0' and comp_economics.state = '1' ".$where.") as t   
                                                                on t.cid = pro_cvalues.pid )
                                            on codevalues.id = pro_cvalues .cvid 
                        where codetype_id =(select id from codetypes where type='".$industry."')  
                        group by codevalues.id,codevalues.value
                        order by codevalues.sort asc";
        //企业总的产值 销售收入 利税总额 利润总额(总)
        $total_ecnomic_sql = "select companies.id as cid,comp_economics.prospect as prospect,comp_economics.general_income as income,
                              comp_economics.pro_taxe as tax,comp_economics.net_profit as profit 
                              from companies join comp_economics on companies.id = comp_economics.cid
                              where companies.id in (select pro_cvalues.pid from pro_cvalues)
                              and companies.is_deleted = '0' and comp_economics.is_deleted = '0' and comp_economics.state = '1' ".$where;
        
        //查询company_patents表中的每个产业的专利数
        $output_w_1 = "'".$year."-01-01 00:00:00'";
        $wyear = $year;
        $wmonth = $month+1;
        if($wmonth < 10){
            $wmonth = '0'.$wmonth;
        }
        if($wmonth == 13){
            $wyear = $wyear + 1;
            $wmonth = '01';
        }
        $output_w = "'".$wyear."-".$wmonth."-01 00:00:00'";
        $patent_sql =  "select codevalues.id,codevalues.value,codevalues.sort,count(t.pantent_id) as patents
                        from codevalues left join (pro_cvalues join ( select companies.id as cid,company_patents.id as pantent_id
                                                                      from companies left join company_patents on companies.id = company_patents.cid
                                                                      where companies.is_deleted = '0' and company_patents.is_deleted = '0' and (company_patents.state = '2' or company_patents.state = '3')
                                                                      and ((company_patents.authorize_time <".$output_w." and company_patents.authorize_time>".$output_w_1.")or (
                                                                           company_patents.buy_time <".$output_w." and company_patents.buy_time>".$output_w_1." ))
                                                                     ) as t   
                                                                on t.cid = pro_cvalues.pid )
                                             on codevalues.id = pro_cvalues .cvid 
                        where codetype_id =(select id from codetypes where type='".$industry."')  
                        group by codevalues.id,codevalues.value
                        order by codevalues.sort asc";
        //查询company_patents表中的专利数总数(总)
        $total_patent_sql = "select count(company_patents.id) as patents
                             from companies join company_patents on companies.id = company_patents.cid
                             where companies.id in (select pro_cvalues.pid from pro_cvalues) and companies.is_deleted = '0' 
                             and company_patents.is_deleted = '0' and (company_patents.state = '2' or company_patents.state = '3')
                             and ((company_patents.authorize_time <".$output_w." and company_patents.authorize_time>".$output_w_1.") or (
                                    company_patents.buy_time <".$output_w." and company_patents.buy_time>".$output_w_1." ))";
        
        //----------查询comp_technologies表中的高新产品和软件产品件数----------
        $technologyReport = $constant->TechnologyReport;
        $declarate_Status = $constant->Declarate_Status;
        $successDeclare =  $constant->SuccessDeclare;//已立项
        $producttypes = $codeType->getCValues($technologyReport);//科技产出项目的类型
        $decstatus = $codeType->getCValues($declarate_Status);//申报状态
        $software = $codeType->findCodeValueID($constant->TechnologyReport,$constant->SoftwareProduct);
        $high_tech = $codeType->findCodeValueID($constant->TechnologyReport,$constant->HighTechProduct);
        $successDeclareId = $codeType->findCodeValueID($constant->Declarate_Status,$constant->SuccessDeclare);
        
        //查询出产业的高新产品件数
        $ht_product_sql = "select codevalues.id,codevalues.value,codevalues.sort,count(t.product_id) as products
                        from codevalues left join ( pro_cvalues join (	select companies.id as cid,comp_technologies.id as product_id,comp_technologies.type_id as typeid
                                                                        from companies left join comp_technologies on companies.id = comp_technologies.cid
                                                                        where companies.is_deleted = '0' and comp_technologies.is_deleted = '0' and comp_technologies.type_id = '".$high_tech."' 
                                                                              and comp_technologies.declarate_status = '".$successDeclareId."' and comp_technologies.state = '2'
                                                                              and comp_technologies.authorize_time < ".$output_w."  and comp_technologies.authorize_time>".$output_w_1.") as t   
                                                                on t.cid = pro_cvalues.pid )
                                             on codevalues.id = pro_cvalues .cvid 
                        where codetype_id =(select id from codetypes where type='".$industry."')  
                        group by codevalues.id,codevalues.value
                        order by codevalues.sort asc";
        //查询出产业的高新产品件数(总)
        $total_ht_product_sql = "select count(comp_technologies.id) as ht_products
                                 from companies join comp_technologies on companies.id = comp_technologies.cid
                                 where companies.id in (select pro_cvalues.pid from pro_cvalues) and companies.is_deleted = '0' and comp_technologies.is_deleted = '0' 
                                       and comp_technologies.type_id = '".$high_tech."' and comp_technologies.declarate_status = '".$successDeclareId."' and comp_technologies.state = '2'
                                       and comp_technologies.authorize_time < ".$output_w."  and comp_technologies.authorize_time>".$output_w_1;
        
        //查询出产业的软件产品件数
        $sw_product_sql = "select codevalues.id,codevalues.value,codevalues.sort,count(t.product_id) as products
                           from codevalues left join ( pro_cvalues join (select companies.id as cid,comp_technologies.id as product_id,comp_technologies.type_id as typeid
                                                                        from companies left join comp_technologies on companies.id = comp_technologies.cid
                                                                        where companies.is_deleted = '0' and comp_technologies.is_deleted = '0' and comp_technologies.type_id = '".$software."' 
                                                                        and comp_technologies.declarate_status = '".$successDeclareId."' and comp_technologies.state = '2'
                                                                        and comp_technologies.authorize_time < ".$output_w."  and comp_technologies.authorize_time>".$output_w_1.") as t   
                                                                on t.cid = pro_cvalues.pid )
                                             on codevalues.id = pro_cvalues .cvid 
                           where codetype_id =(select id from codetypes where type='".$industry."')  
                           group by codevalues.id,codevalues.value
                           order by codevalues.sort asc";
        //查询出产业的软件产品件数(总)
        $total_sw_product_sql = "select count(comp_technologies.id) as sw_products
                                 from companies join comp_technologies on companies.id = comp_technologies.cid
                                 where companies.id in (select pro_cvalues.pid from pro_cvalues) and companies.is_deleted = '0' and comp_technologies.is_deleted = '0' 
                                       and comp_technologies.type_id = '".$software."' and comp_technologies.declarate_status = '".$successDeclareId."' and comp_technologies.state = '2'
                                       and comp_technologies.authorize_time < ".$output_w."  and comp_technologies.authorize_time>".$output_w_1;
        
        //拼接每个产业的数据
        $ecnomics = $this->query($ecnomic_sql);
        $total_ecnomics = $this->query($total_ecnomic_sql);
        $patents = $this->query($patent_sql);
        $total_patents = $this->query($total_patent_sql);
        $ht_products = $this->query($ht_product_sql);
        $total_ht_products = $this->query($total_ht_product_sql);
        $sw_products = $this->query($sw_product_sql);
        $total_sw_products = $this->query($total_sw_product_sql);
        $totaldatas = array();
        $i=0;
        foreach ($ecnomics as $ecnomic){
            $totaldatas[$i]["cvid"]= $ecnomic["codevalues"]["id"];
            $totaldatas[$i]["cvalue"]= $ecnomic["codevalues"]["value"];
            $totaldatas[$i]["prospect"]=$ecnomic[0]["prospect"];
            $totaldatas[$i]["income"]=$ecnomic[0]["income"];
            $totaldatas[$i]["tax"]=$ecnomic[0]["tax"];
            $totaldatas[$i]["profit"]=$ecnomic[0]["profit"];
            $i++;
        }
        $total_prospect = 0;
        $total_income = 0;
        $total_tax = 0;
        $total_profit = 0;
        foreach($total_ecnomics as $total_ecnomic){
            $total_prospect += $total_ecnomic["comp_economics"]["prospect"];
            $total_income += $total_ecnomic["comp_economics"]["income"];
            $total_tax += $total_ecnomic["comp_economics"]["tax"];
            $total_profit += $total_ecnomic["comp_economics"]["profit"];
        }
        $i=0;
        foreach ($patents as $patent){
            $totaldatas[$i]["patents"]=$patent[0]["patents"];
            $i++;
        }
        $i=0;
        foreach ($ht_products as $ht_product){
            $totaldatas[$i]["ht_products"]=$ht_product[0]["products"];
            $i++;
        }
        $i=0;
        foreach ($sw_products as $sw_product){
            $totaldatas[$i]["sw_products"]=$sw_product[0]["products"];
            $i++;
        }
        $totaldatas["total"]["prospect"] = $total_prospect;
        $totaldatas["total"]["income"] = $total_income;
        $totaldatas["total"]["tax"] = $total_tax;
        $totaldatas["total"]["profit"] = $total_profit;
        $totaldatas["total"]["patents"] = $total_patents[0][0]["patents"];
        $totaldatas["total"]["ht_products"] = $total_ht_products[0][0]["ht_products"];
        $totaldatas["total"]["sw_products"] = $total_sw_products[0][0]["sw_products"];;
        return $totaldatas;
    }
    
    function compdata($year, $month,$where=""){
//        $where_1 = "'".$year."-01-01'";
//        $wyear = $year;
//        $wmonth = $month+1;
//        if($wmonth < 10){
//            $wmonth = '0'.$wmonth;
//        }
//        if($wmonth == 13){
//            $wyear = $wyear + 1;
//            $wmonth = '01';
//        }
//        $where= "'".$wyear."-".$wmonth."-01'";
        //$where = "and comp_economics.company_year = '".$year."' and comp_economics.month <= '".$month."'";
        //查询companies\comp_economics表中的每个公司的产值 销售收入 利税总额 利润总额
//        $ecnomic_sql = "select companies.id , companies.company_name, sum(comp_economics.gross_output) as income,sum(comp_economics.pro_taxe) as tax,
//                               sum(comp_economics.net_profit) as profit
//                        from companies left join comp_economics on companies.id = comp_economics.cid and comp_economics.is_deleted = '0' and comp_economics.state = '1'
//                        where companies.is_deleted = '0' and companies.resident_time<".$where." and companies.resident_time >=".$where_1."
//                        group by companies.id";

        $ecnomic_sql = "select companies.id , companies.company_name, OUTEC.general_income as income,OUTEC.pro_taxe as tax,
    						   OUTEC.net_profit as profit
						from companies inner join comp_economics as OUTEC on companies.id = OUTEC.cid and OUTEC.is_deleted = '0' and OUTEC.state = '1'
						and OUTEC.company_year = '".$year."' and OUTEC.month = '".$month."'
						where companies.is_deleted = '0' .$where
						group by companies.id";
    	
        $compecnomics = $this->query($ecnomic_sql);
        return $compecnomics;
    }
    
    //设置公司权限$company["groupid"]!="529ebddb-a1d4-464f-85d8-0b64cbdd56cb"&&$company["groupid"]!="529ebde5-a954-42ee-b9f3-0b64cbdd56cb"
    function compAthority($company){
    	if($company["groupid"]!="529ebddb-a1d4-464f-85d8-0b64cbdd56cb"&&$company["groupid"]!="529ebde5-a954-42ee-b9f3-0b64cbdd56cb"){
    		return true;
    	}else{
    		return false;
    	}
    }
    

    
    
}
?>
